Business Intelligence generally refers to software tools used to improve business enterprise decision-making. These tools are commonly applied to financial, human resource, marketing, sales, customer, and supplier analyses. More specifically, these tools can include reporting and analysis tools to present information, content delivery infrastructure systems to deliver and manage reports and analytics, data warehousing systems to cleanse and consolidate information from disparate sources, and database management systems (“DBMS”) that are used to organize, store, retrieve, and manage data in databases, such as relational, Online Transaction Processing (“OLTP”) and Online Analytic Processing (“OLAP”) databases.
In many organizations data is stored in multiple data sources that are not readily compatible. Each type of data source may be used for different purposes, with, in general. OLTP databases used to store transaction-oriented data, relational databases used to store and organize data according to data relations, and OLAP databases used to store data requiring analytical processing. For example, organizational data for a sales department may be distributed among an OLTP database for storing real-time sales transactions, a relational database for storing data pertaining to customers, and an OLAP database for storing sales history data according to product, geographical regions, and time period. Retrieving sales data for analysis may therefore require multiple queries to multiple databases.
The efficacy of a query in producing a result often depends on the storage structure of the underlying data source. Because OLAP databases are designed to store multi-dimensional data in summarized or aggregated form, they can respond quickly. OLTP and relational databases may have to process tens of thousands of individual records to answer the same query.
Using the sales example above, consider a marketing manager trying to learn why the sales of a certain product were not profitable during a given time period. The manager may browse an OLAP data cube to narrow the profitability problem down to the most detailed information in the cube. The manager may learn that during one specific month, the product's profitability was significantly low in the West Coast region. If the manager were to use an OLTP or relational database to answer the same query, the sales transactions for all customers stored in the database would have to be added before determining which region was responsible for the low profits.
Now suppose the manager wants to investigate which customers and sales representatives were involved in the West Coast transactions during the low profit period. Since the OLAP database only provides aggregated data, raw data items that have not been included in the aggregation would necessarily require the manager to query the OLTP and/or relational databases.
These databases are, therefore, complimentary. A user must be able to navigate between them to solve business problems. For example, a user must be able to “drill-down” from one database to another to acquire more details on a specific data object. Conversely, a user must also be able to “drill-up” from one database to another to reduce the level of detail regarding the object. In doing so, it would be advantageous to insulate the user from the complexities of the underlying data sources.
Currently-available DBMSs tend to provide limited drill-through and drill-up capabilities. For example, OLAP servers such as Analysis Services provided by Microsoft Corp. of Redmond, Wash., Essbase Analytics provided by Hyperion Solutions Corp. of Santa Clara, Calif., and Oracle Business Intelligence Discoverer provided by Oracle Corp. of Redwood Shores, Calif., support only the simplest drill-through scenarios between two OLAP and relational data sources with proprietary API or query language extensions. Drill-through is performed only to a raw SQL table without reaching more basic reporting levels. Some configurations may even require that drill-through results be a part of the OLAP data cube, thereby increasing the cube complexity and data size as the cube must include additional attributes and measures. In addition, these OLAP servers may also pose security risks due to a lack of user access control over drill-through capabilities.
Because of these limitations, users have not been able to leverage the complimentary aspects of the different data sources to their full advantage. There is no business intelligence tool available today, that, either alone or working in tandem, offers users full navigation between multiple data sources without limitation on the number and type of data sources. There also is no business intelligence tool that provides full transparency of execution when performing drill-down or drill-up actions. As a result, managing the data needs of a business enterprise that deals with large amounts of data spread across multiple data sources with different storage structures can be, at best, cumbersome.